0除算でもNULL除算でも0を返すSnowflakeの除算関数「DIV0NULL」を試してみた #SnowflakeDB

※本エントリは、Snowflakeをもっと使いこなそう！ Advent Calendar 2022の９日目の記事となります。

さがらです。

2022年11月のアップデートで、DIV0NULLという関数が追加されました。

本記事ではこの関数を試してみたので、その内容をまとめてみます。

DIV0NULL関数とは

一言でいうと、除算（割り算）を行う場合、分母が「0」や「NULL」であってもエラーやNULLを返すことなく、「0」を返す関数です。

例えば、下図のようにselect 1/0を実行すると、「ゼロでの除算」というエラーメッセージが返ってきます。このエラーを回避するための関数が、DIV0NULL関数となります。

試してみた

下記の公式Docに記載のサンプルに沿って、DIV0NULL関数を試してみます。

使い方としては、引数の１つ目に分子、２つ目に分母、に当たる値を入れればOKです。

例えば、select 1/2とselect div0null(1, 2)は同じ結果を返します。

続いて、0除算を実行してみます。select 1/0の場合は前述の通りエラーメッセージが返ってきますが、select div0null(1, 0)の場合には、「0」が返ってきました。

また、NULL除算も試してみます。select 1/nullの場合はNULLが返ってきてしまいますが、select div0null(1, null)の場合には、「0」が返ってきました。

ちなみに、NULLを分子に持って来る場合、DIV0NULL関数はNULLを返すので注意です。

どういった時に使うのか？

この関数ですが、関数自体の説明と被ってしまうのですが、除算時に分母が0であろうとNULLであろうと、結果はエラーでもNULLでもなく「0」を返したい場合に使えます。

具体的には、除算結果に対してCASE句やWHERE句などで分岐や絞り込みを行うとき、結果にnullが紛れていると適切に処理できないケースがあると思います。そんな時に便利な関数だと感じました。

実はSnowflakeには、元々DIV0という関数がありました（いつからDIV0関数があったかは私もわかっていないのですが…）

DIV0関数ですが、0除算の場合は0を返し、NULL除算の場合はNULLを返す、という関数です。

また、分子にNULLがある場合はDIV0関数でもDIV0NULL関数でもNULLをを返してしまうのですが、そんなときはNVLという関数が使えます。

NVL関数は、第1引数に入れた結果がNULLの場合、第2引数に入れた結果を返す関数のため、nvl(null/1, 0)のように記述すると、分子がNULLであっても0を返してくれます。

また、NVL関数について、0除算の場合はエラーを返してしまうので、少し面倒ですがnvl(div0null(1,0), 0)のように記述してあげれば、エラーをなくした上で、NULLが返ってくることはなくなります。

以上を踏まえると、使い分けのイメージは以下の4パターンかと思います。

• 分母に0があったらエラーを返してほしい
  • 一般的な除算である「1/0」のような表記で記述する
• 分母に0があったら0を返してほしいが、分母にNULLがある場合にはNULLを返してほしい
  • DIV0関数を使用する
• 分母に0やNULLがある場合でも、0を返してほしい
  • DIV0NULL関数を使用する
• 分子にnullがある場合でも、分母に0やNULLがある場合でも、0を返してほしい
  • NVL関数とDIV0NULL関数を併用し、nvl(div0null(1,0), 0)のように記述する

最後に

0除算でもNULL除算でも0を返すSnowflakeの除算関数DIV0NULLを試してみました。

思いの外、NULL周りを制御する関数が多く、奥が深い領域だな～と感じた検証でした。